Group by

Hi.

In a mysql query, it is recommended that "GROUP BY" fields be indexed? =
Using EXPLAIN on one of my queries, I noticed no change between having =
indexed by GROUP BY field and not indexing it. Any thoughts would be =
appreciated.

In this example, should `history_field` be indexed...?

SELECT MAX(`timestamp`) AS `max_ts`=20
FROM `history`=20
WHERE `req_id` =3D 17 AND `history_record_id` =3D 35
GROUP BY `history_field`

Thanks,
~Philip=

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Group by

On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson w=
rote:
> Hi.
>
> In a mysql query, it is recommended that "GROUP BY" fields be indexed? Us=
ing EXPLAIN on one of my queries, I noticed no change between having indexe=
d by GROUP BY field and not indexing it. Any thoughts would be appreciated.
>
> In this example, should `history_field` be indexed...?
>
> SELECT MAX(`timestamp`) AS `max_ts`
> FROM `history`
> WHERE `req_id` =3D 17 AND `history_record_id` =3D 35
> GROUP BY `history_field`

always depends on how many records you have, if you have 100-1000
records is very diferent to 100000-500000


>
> Thanks,
> ~Philip
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>



--=20
Julio Araya C. Linux User #3=
86141
Memorista de Ingenier=EDa Civil Inform=E1tica Ubuntu User #14778
Universidd T=E9cnica Federico Santa Mar=EDa Valpara=EDso - Chile

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Group by

Hi i recommend always use indexes when programming. developers tend not
to.. and when the databases grows it's difficult to modify or make them
modify the code.



El mié, 09-12-2009 a las 14:22 -0300, Julio Araya escribió:
> On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson wrote:
> > Hi.
> >
> > In a mysql query, it is recommended that "GROUP BY" fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated.
> >
> > In this example, should `history_field` be indexed...?
> >
> > SELECT MAX(`timestamp`) AS `max_ts`
> > FROM `history`
> > WHERE `req_id` = 17 AND `history_record_id` = 35
> > GROUP BY `history_field`
>
> always depends on how many records you have, if you have 100-1000
> records is very diferent to 100000-500000
>
>
> >
> > Thanks,
> > ~Philip
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>
>
> --
> Julio Araya C. Linux User #386141
> Memorista de Ingeniería Civil Informática Ubuntu User #14778
> Universidd Técnica Federico Santa María Valparaíso - Chile
>



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Group by

--000e0cd13810f558f4047a4f67b8
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Also you may not see a difference because your not even using
'History_field' anywhere in your SELECT statement. I am a little surprised
that you didn't receive a error.

On Wed, Dec 9, 2009 at 9:31 AM, Juan Pablo Ramirez <
ramirez.juanpablo@gmail.com> wrote:

> Hi i recommend always use indexes when programming. developers tend not
> to.. and when the databases grows it's difficult to modify or make them
> modify the code.
>
>
>
> El mi=E9, 09-12-2009 a las 14:22 -0300, Julio Araya escribi=F3:
> > On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson <
> philthathril@gmail.com> wrote:
> > > Hi.
> > >
> > > In a mysql query, it is recommended that "GROUP BY" fields be indexed=
?
> Using EXPLAIN on one of my queries, I noticed no change between having
> indexed by GROUP BY field and not indexing it. Any thoughts would be
> appreciated.
> > >
> > > In this example, should `history_field` be indexed...?
> > >
> > > SELECT MAX(`timestamp`) AS `max_ts`
> > > FROM `history`
> > > WHERE `req_id` =3D 17 AND `history_record_id` =3D 35
> > > GROUP BY `history_field`
> >
> > always depends on how many records you have, if you have 100-1000
> > records is very diferent to 100000-500000
> >
> >
> > >
> > > Thanks,
> > > ~Philip
> > > --
> > > PHP Database Mailing List (http://www.php.net/)
> > > To unsubscribe, visit: http://www.php.net/unsub.php
> > >
> > >
> >
> >
> >
> > --
> > Julio Araya C. Linux Use=
r
> #386141
> > Memorista de Ingenier=EDa Civil Inform=E1tica Ubuntu User #147=
78
> > Universidd T=E9cnica Federico Santa Mar=EDa Valpara=EDso - Chil=
e
> >
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--=20

=3D"88"
height=3D"31">

--000e0cd13810f558f4047a4f67b8--

Re: Group by

The only SELECT is on MAX('timestamp'). There is really nothing to
Group BY in this query.

Dewey

Philip Thompson wrote:
> Hi.
>
> In a mysql query, it is recommended that "GROUP BY" fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated.
>
> In this example, should `history_field` be indexed...?
>
> SELECT MAX(`timestamp`) AS `max_ts`
> FROM `history`
> WHERE `req_id` = 17 AND `history_record_id` = 35
> GROUP BY `history_field`
>
> Thanks,
> ~Philip
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Group by

--Apple-Mail-8-760626392
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

On Dec 9, 2009, at 12:58 PM, host@deweywilliams.com wrote:

> The only SELECT is on MAX('timestamp'). There is really nothing to =
Group BY in this query.
>=20
> Dewey
>=20
> Philip Thompson wrote:
>> Hi.
>>=20
>> In a mysql query, it is recommended that "GROUP BY" fields be =
indexed? Using EXPLAIN on one of my queries, I noticed no change between =
having indexed by GROUP BY field and not indexing it. Any thoughts would =
be appreciated.
>>=20
>> In this example, should `history_field` be indexed...?
>>=20
>> SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` =3D =
17 AND `history_record_id` =3D 35
>> GROUP BY `history_field`
>>=20
>> Thanks,
>> ~Philip

Well, that was just an example query. My real one is....

SELECT `h`.*
FROM (
SELECT MAX(`history_timestamp`) AS `max_ts`
FROM `history`
WHERE `req_id` =3D 17 AND `history_record_id` =3D 35
GROUP BY `history_field`
) AS `max`
INNER JOIN `history` `h` ON `max`.`max_ts` =3D =
`h`.`history_timestamp`
WHERE `req_id` =3D 17 AND `history_record_id` =3D 35
GROUP BY `history_field`

This returns the results I need. The explain (split up) from this query =
is...

=
+----+-------------+------------+------+-------------------- --------------=
----------+
| id | select_type | table | type | possible_keys =
|
=
+----+-------------+------------+------+-------------------- --------------=
----------+
| 1 | PRIMARY | h | ref | =
req_id_history_record_id,history_timestamp |
| 1 | PRIMARY | | ALL | NULL =
|
| 2 | DERIVED | history | ref | req_id_history_record_id =
|
=
+----+-------------+------------+------+-------------------- --------------=
----------+

=
--------------------------+---------+-------------+------+-- --------------=
------------------------------+
key | key_len | ref | rows | Extra =
|
=
--------------------------+---------+-------------+------+-- --------------=
------------------------------+
req_id_history_record_id | 8 | const,const | 3 | Using =
temporary; Using filesort |
NULL | NULL | NULL | 2 | Using where =
|
req_id_history_record_id | 8 | | 3 | Using where; =
Using temporary; Using filesort |
=
--------------------------+---------+-------------+------+-- --------------=
------------------------------+ =20
3 rows in set (0.01 sec)

There's only 10 records in table right now... but the # of rows it's =
going to traverse before find the results is very small.=20

Do I need to include `history_field` in the inner select?

Thanks,
~Philip=

--Apple-Mail-8-760626392--

Re: Group by

Philip Thompson wrote:
> On Dec 9, 2009, at 12:58 PM, host@deweywilliams.com wrote:
>
>> The only SELECT is on MAX('timestamp'). There is really nothing to Group BY in this query.
>>
>> Dewey
>>
>> Philip Thompson wrote:
>>> Hi.
>>>
>>> In a mysql query, it is recommended that "GROUP BY" fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated.

You won't with only 10 rows in the table.

>>> In this example, should `history_field` be indexed...?
>>>
>>> SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35
>>> GROUP BY `history_field`

I'd index
req_id, history_record_id, history_field, timestamp

If you're using myisam tables then all the data can be fetched directly
from the index instead of hitting the data table as well. If you're
using innodb, it'll at least use this index to search for req_id and
history_record_id (assuming these fields are normally in your queries).

> There's only 10 records in table right now... but the # of rows it's going to traverse before find the results is very small.

In theory. Sometimes databases don't work that way and instead of
choosing a particular index you'd expect it to, it'll pick another one.
Fill up the table(s) and make sure it does what you expect. Mysql isn't
great at subselects either.

> Do I need to include `history_field` in the inner select?

No, you don't have to.

You could do a query like:

select count(id) from table group by another_field;

so you get a count per another_field of how many records there are. Not
a great example as normally you would include another_field in the
select, but you don't have to.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php